using System;
using System.Data;
using System.Text;

using Elev.Core.Data;
using Elev.Core.Utility;

namespace Elev.Core.DAL
{
    internal class PeriodInternal : SqliteTable
    {
        internal bool DisplayOrderExists(int DisplayOrder)
        {
            return DisplayOrderExists(DisplayOrder, -1);
        }
        
        internal bool DisplayOrderExists(int DisplayOrder, int ExcludedPeriodID)
        {
            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM Period WHERE DisplayOrder = @DisplayOrder");
            if (ExcludedPeriodID > -1)
            {
                sql.Append(" AND ID != @ID");
                return (Convert.ToInt32(SqliteAdapter.Get().ExecuteScalar(sql.ToString(), "@DisplayOrder", DisplayOrder, "@ID", ExcludedPeriodID)) != 0);
            }
            else
            {
                return (Convert.ToInt32(SqliteAdapter.Get().ExecuteScalar(sql.ToString(), "@DisplayOrder", DisplayOrder)) != 0);
            }
        }

        /// <summary>
        /// Returns a DataTable containing all the periods in the database
        /// </summary>
        /// <returns></returns>
        internal DataTable GetPeriods()
        {
            string SQL = "SELECT * FROM Period ORDER BY DisplayOrder";
            return SqliteAdapter.Get().Execute(SQL);
        }

        /// <summary>
        /// Returns a DataTable containing one given period
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        internal DataTable GetPeriod(int ID)
        {
            string SQL = "SELECT * FROM Period WHERE ID = @ID";
            return SqliteAdapter.Get().Execute(SQL, "@ID", ID);
        }

        internal int Add(string Name, bool ShowAvgByType, bool ShowGlobalAvg, int DisplayOrder)
        {
            if (Name == "")
                throw new ArgumentException("To add a period, a name is required.");

            if (DisplayOrder < 1 || DisplayOrderExists(DisplayOrder))
                throw new ArgumentException("To add a period, a valid display order must be provided.");

            string SQL = "INSERT INTO Period(Name, ShowAvgByType, ShowGlobalAvg, DisplayOrder) VALUES(@Name, @ShowAvgByType, @ShowGlobalAvg, @DisplayOrder)";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@Name", Name, "@ShowAvgByType", (ShowAvgByType ? 1 : 0), 
                "@ShowGlobalAvg", (ShowGlobalAvg ? 1 : 0), "@DisplayOrder", DisplayOrder);

            return Convert.ToInt32(SqliteAdapter.Get().ExecuteScalar("SELECT MAX(ID) FROM Period"));
        }

        internal void Update(int ID, string Name, bool ShowAvgByType, bool ShowGlobalAvg, int DisplayOrder)
        {
            if (Name == "")
                throw new ArgumentException("To update a period, a name is required.");

            if (DisplayOrder < 1 || DisplayOrderExists(DisplayOrder, ID))
                throw new ArgumentException("To add a period, a valid display order must be provided.");

            string SQL = "UPDATE Period SET Name = @Name, ShowAvgByType = @ShowAvgByType, ShowGlobalAvg = @ShowGlobalAvg, DisplayOrder = @DisplayOrder WHERE ID = @ID";
            SqliteAdapter.Get().ExecuteNonQuery(SQL, "@Name", Name, "@ShowAvgByType", (ShowAvgByType ? 1 : 0), 
                "@ShowGlobalAvg", (ShowGlobalAvg ? 1 : 0), "@DisplayOrder", DisplayOrder, "@ID", ID);
        }

        internal void Delete(int ID)
        {
            StringBuilder sql = new StringBuilder();
           
            sql.Append("DELETE FROM Grade WHERE ExamID in (SELECT ID FROM Exam WHERE PeriodID = @PeriodID)");
            SqliteAdapter.Get().ExecuteNonQuery(sql.ToString(), "@PeriodID", ID);

            sql = new StringBuilder();
            sql.Append("DELETE FROM Exam WHERE PeriodID = @PeriodID");
            SqliteAdapter.Get().ExecuteNonQuery(sql.ToString(), "@PeriodID", ID);

            sql = new StringBuilder();
            sql.Append("DELETE FROM Period WHERE ID = @ID");
            SqliteAdapter.Get().ExecuteNonQuery(sql.ToString(), "@ID", ID);
        }

        #region SqliteTable Members

        public override void Create()
        {
            string SQL = "CREATE TABLE [Period] (" +
                            " ID INTEGER DEFAULT '' NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE," +
                            " Name VARCHAR(50) DEFAULT '' NOT NULL," +
                            " ShowAvgByType INTEGER DEFAULT '1' NOT NULL," +
                            " ShowGlobalAvg INTEGER DEFAULT '1' NOT NULL," +
                            " DisplayOrder INTEGER DEFAULT '' NOT NULL)";

            SqliteAdapter.Get().ExecuteNonQuery(SQL);
        }

        public override void Drop()
        {
            string SQL = "DROP TABLE [Period]";
            SqliteAdapter.Get().ExecuteNonQuery(SQL);
        }

        #endregion
    }
}
